package com.zretc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.sun.org.glassfish.gmbal.ManagedAttribute;
import com.zretc.dao.OrdersDao;
import com.zretc.entity.OrderDetail;
import com.zretc.entity.Orders;
import com.zretc.entity.Product;
import com.zretc.entity.Seller;
import com.zretc.entity.User;
import com.zretc.util.DBUtil;
import com.zretc.util.PageInfo;

/**
 * @author chenbo
 * 订单表dao层的实现类
 */
public class OrdersDaoImpl implements OrdersDao{

	@Override
	public PageInfo<Orders> findOrderAllByUserId(Map<String, String> params) {
		String userIdKey = params.get("userId");
		String keyword = params.get("keyword");
		Integer orderStatusKey = Integer.valueOf(params.get("orderStatus"));
		
		Integer pageNum = Integer.valueOf(params.get("pageNum"));
		Integer pageSize = Integer.valueOf(params.get("pageSize"));
				
		List<Orders> data = new ArrayList<>();
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb.append(" select")
		.append(" order_id,")
		.append(" user_id,")
		.append(" order_time,")
		.append(" order_total_price,")		
		.append(" order_status")
		.append(" from orders")
		.append(" where user_id = ?")
		.append(" and order_id like ?");
		if (orderStatusKey == 0) {		
			sb.append(" order by order_time desc");
			sb.append(" limit ?, ?");
			rs = DBUtil.doQuery(sb.toString(),userIdKey, keyword, (pageNum-1)*pageSize, pageSize);
		} else if (orderStatusKey != 0){
			sb.append(" and order_status = ?");
			sb.append(" order by order_time desc");
			sb.append(" limit ?, ?");	
			rs = DBUtil.doQuery(sb.toString(),userIdKey, keyword, orderStatusKey, (pageNum-1)*pageSize, pageSize);
		}		
		try {
			while (rs.next()) {
				String orderId = rs.getString("order_id");
				Integer userId = rs.getInt("user_id");
				String orderTime = rs.getString("order_time");
				Float orderTotalPrice = rs.getFloat("order_total_price");
				Integer orderStatus = rs.getInt("order_status");
				Orders orders = new Orders();
				orders.setOrderId(orderId);
				User user = new User();
				user.setUserId(userId);
				orders.setUser(user);
				orders.setOrderTime(orderTime);
				orders.setOrderTotalPrice(orderTotalPrice);
				orders.setOrderStatus(orderStatus);
				data.add(orders);
														
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}// 查询分页前的总记录数
		Integer total = 0;	
		
		sb = new StringBuilder();
		sb.append("select count(*)")
		.append(" from orders")
		.append(" where user_id = ?")
		.append(" and order_id like ?");
		if (orderStatusKey == 0) {
			rs = DBUtil.doQuery(sb.toString(),userIdKey, keyword);
		} else if (orderStatusKey != 0) {
			sb.append(" and order_status = ?");
			rs = DBUtil.doQuery(sb.toString(),userIdKey, keyword, orderStatusKey);
		}
		try {
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageInfo<Orders> pageInfo = new PageInfo<>(data, pageNum, pageSize, total);
		return pageInfo;
	}


	@Override
	public PageInfo<Map<String, Object>> findPageOrderAll(Map<String, String> params) {
		String sellerIdKey = params.get("sellerIdKey");
		String keyword = params.get("keyword");
		Integer orderStatusKey = Integer.valueOf(params.get("orderStatusKey"));

		Integer pageNum = Integer.valueOf(params.get("pageNum"));
		Integer pageSize = Integer.valueOf(params.get("pageSize"));
		
		List<Map<String, Object>> data = new ArrayList<>();
	
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb.append(" select")
		.append(" B.order_id,")
		.append(" sum(B.order_detail_price),")
		.append(" A.order_time,")
		.append(" B.order_detail_status,")
		.append(" C.seller_id,")
		.append(" D.user_account")
		.append(" from orders A")
		.append(" inner join order_detail B")
		.append(" on A.order_id = B.order_id")
		.append(" inner join product C")
		.append(" on B.product_id = C.product_id")
		.append(" inner join user D")
		.append(" on A.user_id = D.user_id")
		.append(" where C.seller_id = ?")
		.append(" and B.order_id like ?");				
		if (orderStatusKey == 0) {	
			sb.append(" group by B.order_id");
			sb.append(" order by A.order_time desc");
			sb.append(" limit ?, ?");	
			rs = DBUtil.doQuery(sb.toString(),sellerIdKey, keyword, (pageNum-1)*pageSize, pageSize);
		}else if (orderStatusKey != 0){			
			sb.append(" and B.order_detail_status = ?");
			sb.append(" group by B.order_id");
			sb.append(" order by A.order_time desc");
			sb.append(" limit ?, ?");	
			rs = DBUtil.doQuery(sb.toString(),sellerIdKey, keyword, orderStatusKey, (pageNum-1)*pageSize, pageSize);
		}		
		try {
			while (rs.next()) {
				String orderId = rs.getString("order_id");
				Integer orderDetailStatus = rs.getInt("order_detail_status");
				Float orderTotalPrice = rs.getFloat("sum(B.order_detail_price)");
				String orderTime = rs.getString("order_time");				
				String userAccount = rs.getString("user_account");
				Map<String, Object> map = new HashMap<>();
				map.put("orderId", orderId);
				map.put("orderDetailStatus", orderDetailStatus);
				map.put("orderTotalPrice", orderTotalPrice);
				map.put("orderTime", orderTime);
				map.put("userAccount",userAccount);
				data.add(map);						
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// 查询分页前的总记录数
		Integer total = 0;	
		
		sb = new StringBuilder();
		sb.append("select count(*)")
		.append(" from orders A")
		.append(" inner join order_detail B")
		.append(" on A.order_id = B.order_id")
		.append(" inner join product C")
		.append(" on B.product_id = C.product_id")
		.append(" inner join user D")
		.append(" on A.user_id = D.user_id")
		.append(" where C.seller_id = ?")
		.append(" and B.order_id like ?");
		if (orderStatusKey == 0) {
			rs = DBUtil.doQuery(sb.toString(),sellerIdKey, keyword);
		} else if (orderStatusKey != 0) {
			sb.append(" and B.order_detail_status = ?");
			rs = DBUtil.doQuery(sb.toString(),sellerIdKey, keyword, orderStatusKey);
		}
		try {
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageInfo<Map<String, Object>> pageInfo = new PageInfo<>(data, pageNum, pageSize, total);
		return pageInfo;
	}
	
	@Override
	public List<Map<String, Object>> findOrderByorderId(String orderId) {
		List<Map<String, Object>> list = new ArrayList<>();
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb.append(" select")
		.append(" distinct A.order_id,")
		.append(" B.order_status,")
		.append(" B.order_receive_address,")
		.append(" B.order_receive_name,")
		.append(" B.order_receive_phone,")
		.append(" C.user_account")
		.append(" from order_detail A")
		.append(" inner join orders B")
		.append(" on A.order_id = B.order_id")
		.append(" inner join user C")
		.append(" on B.user_id = C.user_id")
		.append(" where A.order_id = ?");
		rs = DBUtil.doQuery(sb.toString(), orderId);
		try {
			while (rs.next()) {
				Integer orderStatus = rs.getInt("order_status");
				String orderReceiveAddress = rs.getString("order_receive_address");
				String orderReceiveName = rs.getString("order_receive_name");
				String orderReceivePhone = rs.getString("order_receive_phone");
				String userAccount = rs.getString("user_account");
				Map<String, Object> map = new HashMap<>();
				map.put("orderStatus", orderStatus);
				map.put("orderReceiveAddress", orderReceiveAddress);
				map.put("orderReceiveName", orderReceiveName);
				map.put("orderReceivePhone", orderReceivePhone);
				map.put("userAccount", userAccount);
				list.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<OrderDetail> findOrderDetailByorderId(String orderId, Integer sellerId) {
		List<OrderDetail> list = new ArrayList<>();
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb.append(" select")
		.append(" A.order_detail_id,")
		.append(" A.order_id,")
		.append(" B.product_name,")
		.append(" A.order_detail_count,")
		.append(" A.order_detail_price,")
		.append(" A.order_detail_status")
		.append(" from order_detail A")
		.append(" inner join product B")
		.append(" on A.product_id = B.product_id")
		.append(" inner join seller C")
		.append(" on B.seller_id = C.seller_id")
		.append(" where A.order_id = ?")
		.append(" and C.seller_id = ?");
		rs = DBUtil.doQuery(sb.toString(), orderId, sellerId);
		try {
			while (rs.next()) {
				Integer orderDetailId = rs.getInt("order_detail_id");
				String productName = rs.getString("product_name");
				Integer orderDetailCount = rs.getInt("order_detail_count");
				Float orderDetailPrice = rs.getFloat("order_detail_price");
				Integer orderDetailStatus = rs.getInt("order_detail_status");
				OrderDetail orderDetail = new OrderDetail();
				orderDetail.setOrderDetailId(orderDetailId);
				Product product = new Product();
				product.setProductName(productName);
				orderDetail.setProduct(product);
				orderDetail.setOrderDetailCount(orderDetailCount);
				orderDetail.setOrderDetailPrice(orderDetailPrice);
				orderDetail.setOrderDetailStatus(orderDetailStatus);
				list.add(orderDetail);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int deleteOrder(String orderId) {
		String sql = "delete from orders where order_id = ?";
		return DBUtil.doUpdate(sql, orderId);
	}

	@Override
	public int insertOrder(Orders orders) {
		String sql = "insert into orders(order_id, user_id, order_total_price, order_time, order_status, order_pay_method, order_receive_address, order_receive_name, order_receive_phone) "
				+ " values(?,?,?,?,?,?,?,?,?)";
		int doUpdate = DBUtil.doUpdate(sql, orders.getOrderId(), orders.getUser().getUserId(), orders.getOrderTotalPrice(), orders.getOrderTime(), orders.getOrderStatus(), orders.getOrderPayMethod(), orders.getOrderReceiveAddress(), orders.getOrderReceiveName(), orders.getOrderReceivePhone());
		return doUpdate;
	}
	
	@Override
	public int updateOrderByOrderStatus(Orders orders) {
		String sql = "update orders set order_status = ? where order_id = ?";
		return DBUtil.doUpdate(sql, orders.getOrderStatus(), orders.getOrderId());
	}
	@Override
	public int updateOrderByOrderStatus(OrderDetail orderDetail) {
		String sql = "update order_detail set order_detail_status = ? where order_detail_id = ? and order_id = ?";
		return DBUtil.doUpdate(sql, orderDetail.getOrderDetailStatus(), orderDetail.getOrderDetailId(), orderDetail.getOrders().getOrderId());
	}


	/**
	 * denglingqi
	 * 商城管理
	 * 后端 订单管理 查询订单信息
	 * @param params 条件参数 分页查询
	 * 根据订单号和店铺名
	 * 订单状态，交易时间
	 * 分页当前页码 pageNum
	 * 分页获取条数 pageSize
	 * @return Order
	 */
	
	@Override
	public PageInfo<Orders> findPageOrder(Map<String, String> params) {
		String  orderIdKey = params.get("orderId");	  		//订单编号
		String  sellerIdKey = params.get("sellerId");		//商铺编号
		String orderTimeKey = params.get("orderTime");		//开始时间
		String orderTimesKey = params.get("orderTimes");	//结束时间
		String orderStatusKey = params.get("orderStatus");	//订单状态
		Integer pageNum = Integer.valueOf(params.get("pageNum"));
		Integer pageSize = Integer.valueOf(params.get("pageSize"));
		Integer orderStatusPage = null;
		if (orderIdKey != null) {
			orderStatusPage = Integer.valueOf(params.get("orderIdKey"));
		}	
		
		List<Orders> data = new ArrayList<>();
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb.append(" select")
		.append(" A.order_id,")
		.append(" C.seller_id,")
		.append(" A.order_status,")
		.append(" A.order_time,")
		.append(" A.order_total_price")
		.append(" from orders A")
		.append(" inner join order_detail B")
		.append(" B on A.order_id=B.order_id")
		.append(" inner join product C")
		.append(" B.product_id=C.product_id")
		.append(" group by A.order_id");
		//查询所有
		if ("".equals(orderIdKey) && "".equals(sellerIdKey) && "".equals(orderTimeKey) && "".equals(orderStatusKey)) {
			sb.append(" limit ?, ?");
			rs = DBUtil.doQuery(sb.toString(),(pageNum-1)*pageSize, pageSize);
		//根据订单编号查询
		} else if (!"".equals(orderIdKey)) {
			sb.append(" where A.order_id='?'");
			sb.append(" limit ?, ?");
			rs = DBUtil.doQuery(sb.toString(),orderIdKey+"%",(pageNum-1)*pageSize, pageSize);
		//根据商铺编号查询
		}else if (!"".equals(sellerIdKey)) {
			sb.append(" where C.seller_id='?'");
			sb.append(" limit ?, ?");
			rs = DBUtil.doQuery(sb.toString(),"%"+sellerIdKey+"%",(pageNum-1)*pageSize, pageSize);	
		//根据订单状态查询
		}else if (!"".equals(orderStatusKey)) {
			sb.append(" where A.order_status='?'");
			sb.append(" limit ?, ?");
			rs = DBUtil.doQuery(sb.toString(),orderStatusKey,(pageNum-1)*pageSize, pageSize);
		//根据交易时间查询
		}else if (!"".equals(orderTimeKey)) {
			sb.append(" where order_time BETWEEN '?' AND '?'");
			sb.append(" limit ?, ?");
			rs = DBUtil.doQuery(sb.toString(),orderTimeKey,orderTimesKey,(pageNum-1)*pageSize, pageSize);
		}
		
		try {
			while (rs.next()) {				
				String orderId = rs.getString("order_id");
				Float orderTotalPrice = rs.getFloat("order_total_price");
				String orderTime = rs.getString("order_time");
				Integer orderStatus = rs.getInt("order_status");
				Integer sellerId = rs.getInt("seller_id");			
				Orders orders = new Orders();
				orders.setOrderId(orderId);
				orders.setOrderTotalPrice(orderTotalPrice);
				orders.setOrderTime(orderTime);
				orders.setOrderStatus(orderStatus);
				Seller seller = new Seller();
				seller.setSellerId(sellerId);
				sellerId = seller.getSellerId();
				data.add(sellerId,orders);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Integer total = 0;		
		sb = new StringBuilder();
		sb.append("select count(*)")
		.append(" from orders ")
		.append(" where A.order_id like ?");
		if (orderStatusPage == null) {
			rs = DBUtil.doQuery(sb.toString(), orderIdKey);
		}
		try {
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageInfo<Orders> pageInfo = new PageInfo<>(data, pageNum, pageSize, total);
		return pageInfo;
	}

}
